package com.r7data.dbstruct.demo;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.r7data.dbstruct.config.DbSource;


public class MysqlMetaTest {

	public Connection getCon() throws Exception {
		String driverClass = "com.mysql.jdbc.Driver";
		String dbUrl = "jdbc:mysql://127.0.0.1:3306/other_test";
		String dbUser = "root";
		String dbPassword = "admin";

		Connection con = DbSource.getCon(driverClass, dbUrl, dbUser, dbPassword);

		return con;
	}

	/**
	 * columnMeta
	 * 
	 * @param dbmd
	 * @param dbName
	 */
	public void getMetaColumn(DatabaseMetaData dbmd, String dbName) {
		System.out.println("");
		System.out.println("===== columnName =====");
		System.out.println("");

		ResultSet rst = null;

		try {
			rst = dbmd.getTables(dbName, null, null, new String[] { "TABLE" });

			ResultSetMetaData rsm = rst.getMetaData();

			int colCount = rsm.getColumnCount();

			for (int i = 0; i < colCount; i++) {
				String columnName = rsm.getColumnName(i + 1);

				System.out.print(columnName + " | ");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		System.out.println("");
	}

	/**
	 * 表
	 * 
	 * @param dbmd
	 * @param dbName
	 * @return
	 */
	public List<String> getTables(DatabaseMetaData dbmd, String dbName) {

		List<String> tableList = new ArrayList<String>();

		System.out.println("");
		System.out.println("===== " + dbName + " tables =====");
		System.out.println("");

		ResultSet rst = null;

		try {
			rst = dbmd.getTables(dbName, null, null, new String[] { "TABLE" });

			while (rst.next()) {

				String tableName = rst.getString(3);

				System.out.println(tableName);

				tableList.add(tableName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		System.out.println("");

		return tableList;
	}

	public List<String> getProColumn(DatabaseMetaData dbmd){
		List<String> proceList = new ArrayList<String>();
		
		ResultSet rst = null;

		try {
			rst = dbmd.getProcedures(null, null, null);
			
			ResultSetMetaData rsm = rst.getMetaData();

			int colCount = rsm.getColumnCount();

			for (int i = 0; i < colCount; i++) {
				String columnName = rsm.getColumnName(i + 1);

				proceList.add(columnName);
				System.out.print(columnName + " | ");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		System.out.println("");
		return proceList;
	}
	
	public void getProcedures(DatabaseMetaData dbmd, String dbName) {
		
		List<String> proceList = getProColumn(dbmd);
		
		System.out.println("");
		System.out.println("===== " + dbName + " procedures =====");
		System.out.println("");
		
		
		ResultSet rst = null;

		try {
			rst = dbmd.getProcedures(dbName, null, null);
		
			while(rst.next()){
				
				String procCatalog = rst.getString("PROCEDURE_CAT");
				String procSchema = rst.getString("PROCEDURE_SCHEM");
				String procName = rst.getString("PROCEDURE_NAME");
				
				System.out.println(procCatalog + " | " + procName);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}

	/**
	 * 遍历视图
	 * 
	 * @param dbmd
	 * @param dbName
	 * @return
	 */
	public List<String> getViews(DatabaseMetaData dbmd, String dbName) {

		List<String> viewList = new ArrayList<String>();

		System.out.println("");
		System.out.println("===== " + dbName + " views =====");
		System.out.println("");

		ResultSet rst = null;

		try {
			rst = dbmd.getTables(dbName, null, null, new String[] { "VIEW" });

			while (rst.next()) {

				String viewName = rst.getString(3);

				System.out.println(viewName);

				viewList.add(viewName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		System.out.println("");

		return viewList;
	}

	/**
	 * 获得数据库
	 * 
	 * @param dbmd
	 * @return
	 */
	public List<String> getDatabase(DatabaseMetaData dbmd) {

		System.out.println("===== databases =====");
		System.out.println("");

		ResultSet rst = null;

		List<String> dbList = new ArrayList<String>();
		try {
			rst = dbmd.getCatalogs();

			while (rst != null && rst.next()) {

				String dbName = rst.getString(1);

				dbList.add(dbName);
				System.out.println(dbName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		System.out.println("");

		return dbList;
	}

	public List<String> getLayoutMeta(){
		
		
		return null;
	}
	
	
	public void getTableLayout(Connection con, String dbName, String tableName){
		
		String sql = "SELECT * FROM " + dbName + "." + tableName + " LIMIT 0";
		
		PreparedStatement pst = null;
		ResultSet rst = null;
		
		try {
			pst = con.prepareStatement(sql);
			rst = pst.executeQuery();
			
			pst.setMaxRows( 1 );
			
			ResultSetMetaData rsmd = pst.getMetaData();
			
			int colCount = rsmd.getColumnCount();
			
			for(int i = 1; i <= colCount; i++){
				
				String columnName = rsmd.getColumnName(i);
				
				/** ========== column meta ========== **/
				
				int type = rsmd.getColumnType(i);
				
				boolean signed = rsmd.isSigned(i);
				
				String typeName = rsmd.getColumnTypeName(i);
				
				/** ========== column meta ========== **/
				//getValueFromSQLType
				
				System.out.print(columnName + " | ");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rst.close();
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	
	/**
	 * 获得 mysql 数据库列表
	 */
	public void getDbStrut() {

		Connection con = null;
		ResultSet rst = null;

		try {
			con = getCon();

			DatabaseMetaData dbmd = con.getMetaData();

			//databaseMeta.supportsSchemas() && dbmd.supportsSchemasInTableDefinitions()
			
			// 数据库
			List<String> databaseList = getDatabase(dbmd);

			// 列名
			getMetaColumn(dbmd, "");

			// 表名
			for (String oneDb : databaseList) {
				getTables(dbmd, oneDb);
			}

			// 视图
			for (String oneDb : databaseList) {
				getViews(dbmd, oneDb);
			}

			//存储过程
			for (String oneDb : databaseList) {
				getProcedures(dbmd, oneDb);
			}
		
			//getProColumn(dbmd);

			
			
			getTableLayout(con, "other_test", "user_info");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			DbSource.closeDb(con, null, rst);
		}

	}

	public static void main(String[] args) {

		MysqlMetaTest mt = new MysqlMetaTest();

		mt.getDbStrut();

		System.out.println("");
		System.out.println("===== finished =====");
	}

}
